package awesome.Manager;

import java.sql.ResultSet;
import java.sql.SQLException;
import java.sql.Statement;
import java.sql.Timestamp;
import java.util.ArrayList;
import java.util.GregorianCalendar;
import java.util.LinkedList;

public class ItemManager extends Manager{
	public ItemManager() {
		super("item");
	}

	public int getCount()
	{
		String query = "select count(*) from item";
		return this.db.getCount(query);
	}

	/**
	 * Inserts a new Item into the Database
	 * 
	 * @param item The item to insert.
	 */
	public void insertItem(int uid,int category,String description,String start, String end, double minBid, String name){
		String query = "INSERT INTO item " +
		"VALUES (Null,'"+description+"','"+uid+"','"+start+"','"+end+"','"+minBid+"','"+category+"','"+name+"')";
		db.execute(query);
	}

	/**
	 * 
	 * @return A list of the item categories
	 */
	public LinkedList<String> getCategories(){
		ResultSet rs = this.db.getResultSet("Select * from category");
		LinkedList<String> list = new LinkedList<String>();
		if(rs != null)
		{
			try {
				while(rs.next())
				{
					list.add(rs.getString("category_name"));				
				}
			} catch (SQLException e) {
				// TODO Auto-generated catch block
				e.printStackTrace();
			}
		}
		return list;
	}

	public Item getItem(int iid){
		String query = "SELECT * FROM item WHERE iid = " + iid;
		ResultSet rs = db.getResultSet(query);
		if(rs != null){
			try {
				if(rs.next()){
					return new Item(rs.getInt("iid"), rs.getInt("uid"), rs.getInt("category"), 
							rs.getString("description"), rs.getString("start_time"), rs.getString("end_time"), rs.getDouble("min_bid"), rs.getString("name"));
				}
			} catch (SQLException e) {
				e.printStackTrace();
			}
		}
		return null;
	}

	public LinkedList<Item> getItemsForUser(int uid){
		String query = "SELECT * FROM item WHERE uid = " + uid;
		ResultSet rs = db.getResultSet(query);
		LinkedList<Item> list = new LinkedList<Item>();
		if(rs != null)
		{
			try {
				while(rs.next()){

					Item i = new Item(rs.getInt("iid"), rs.getInt("uid"), rs.getInt("category"), 
							rs.getString("description"), rs.getString("start_time"), rs.getString("end_time"), rs.getDouble("min_bid"), rs.getString("name"));	
					list.add(i);
				}
			} catch (SQLException e) {
				e.printStackTrace();
			}
		}
		return list;
	}
	
	public LinkedList<Item> getItemsByBidder(int uid){
		String query = "SELECT iid, uid FROM `bid` WHERE uid = " + uid;
		ArrayList<Integer> iids = new ArrayList();
		LinkedList<Item> list = new LinkedList<Item>(); //to be returned
		ResultSet rs = db.getResultSet(query); //returns a result of bids of the user
		int iid=0;
		if(rs != null)
		{
			try {
				while(rs.next()){
					iid = rs.getInt("iid");
					addNonDuplicate(iids, iid);
				}
			} catch (SQLException e) {
				e.printStackTrace();
			}
		}
		//The ArrayList 'iids' now contains the list of iids the user bidded on, with no duplicates
		//Must now retrieve items using the iids
		for (int i = 0; i < iids.size(); i++) {
			query = "SELECT * FROM item WHERE iid = " + iids.get(i).intValue();
			rs = db.getResultSet(query); //returns the item result from one iid
			if(rs != null)
			{
				try {
					if(rs.next()){
						Item item = new Item(rs.getInt("iid"), rs.getInt("uid"), rs.getInt("category"), 
								rs.getString("description"), rs.getString("start_time"), rs.getString("end_time"), rs.getDouble("min_bid"), rs.getString("name"));	
						list.add(item);
					}
				} catch (SQLException e) {
					e.printStackTrace();
				}
			}
		}
		//'list' now contains a non-duplicated list of items the user has bidded on
		return list;
		
	}
	
	
	
/**
 * Returns all items
 * @return
 */
	public ArrayList<Item> getItems() {
		ResultSet rs = this.db.getResultSet("Select * from "+ this.tableName);
		ArrayList<Item> list = new ArrayList<Item>();
		if(rs != null)
		{
			try {
				while(rs.next())
				{

					Item i = new Item(rs.getInt("iid"), rs.getInt("uid"), rs.getInt("category"), 
							rs.getString("description"), rs.getString("start_time"), rs.getString("end_time"), rs.getDouble("min_bid"), rs.getString("name"));

					list.add(i);
					//System.out.println(i);


				}
			} catch (SQLException e) {
				// TODO Auto-generated catch block
				e.printStackTrace();
			}
		}
		return list;
	}
	
	
/**
 * Returns the number of bids on a particular item (all users)
 * @param iid
 * @return
 */
	public int getNumBids(int iid) {
		String query = "SELECT count(*) FROM bid WHERE iid =  " + iid;
		return this.db.getCount(query);
	}
	
/**
 * Returns the highest bid on an item (of all users)
 * @param iid
 * @return
 */
	public double getHighestBid(int iid) {
		String query = "SELECT * FROM bid WHERE iid = " + iid + " ORDER BY amount DESC";
		ResultSet rs = this.db.getResultSet(query);
		try {
			if (rs.next()) 
				return rs.getDouble("amount");
		} catch (SQLException e) {
			e.printStackTrace();
		}
		return 0;
	}
	
	public double getBidOfUser(int iid, int uid) {
		String query = "SELECT * FROM bid WHERE iid = " + iid + " AND uid = " + uid + " ORDER BY amount DESC";
		ResultSet rs = this.db.getResultSet(query);
		try {
			if (rs.next()) 
				return rs.getDouble("amount");
		} catch (SQLException e) {
			e.printStackTrace();
		}
		return 0;
	}

	public String getOwner(int iid) {
		String query = "SELECT * FROM bid WHERE iid = " + iid;
		ResultSet rs = this.db.getResultSet(query);
		int ownerID=0;
		try {
			if (rs.next()) {
				ownerID = rs.getInt("uid"); //get the user id of who owns the item
				query = "SELECT * FROM user WHERE uid = " + ownerID;
				ResultSet rs2 = this.db.getResultSet(query);
				if (rs2.next())
					return rs2.getString("username"); //return the username associated with the user id

			} 
		}catch (SQLException e) {
			e.printStackTrace();
		}
		return null;
	}
	
	/**
	 * Used to add integers to a list only if that number is not already in the list
	 * @param list
	 * @param number
	 * @return
	 */
	private ArrayList<Integer> addNonDuplicate(ArrayList<Integer> list, int number) {
		Integer num = new Integer(number);
		boolean addNum = true;
		for (int i = 0; i < list.size(); i++) {
			if (list.get(i).equals(num)) {
				addNum = false;
				break;
			}
		}
		if (addNum)
			list.add(num);
		return list;
	}


	public static void main(String[] args)
	{
		System.out.println(new ItemManager().getTimeStamp(15));
	}

	public long getTimeStamp(int iid) {
		
		String query = "select * from item where iid = "+iid+"";
		System.out.println(query);
		
		String time;
		try {
			this.db.openConnection();
			Statement stm = this.db.getConnection().createStatement();
			ResultSet rs = stm.executeQuery(query);
			
			rs.next();
			Timestamp timestamp = rs.getTimestamp("end_time");
			
			System.out.println(timestamp);
			System.out.println(timestamp.getYear()+1900 +"   " +
					timestamp.getMonth()+ "   " + timestamp.getDate()+ "   " + timestamp
							.getHours()+ "   " + timestamp.getMinutes()+ "   " + timestamp
							.getSeconds());
			
			GregorianCalendar g1 = new GregorianCalendar(timestamp.getYear()+1900,
					timestamp.getMonth(), timestamp.getDate(), timestamp
							.getHours(), timestamp.getMinutes(), timestamp
							.getSeconds());
			
			System.out.println(g1.getTimeInMillis());
		//	System.out.println(timestamp);
			
		//	System.out.println(new GregorianCalendar(2009, 3, 22, 22, 24,33).getTimeInMillis());
			//System.out.println(System.currentTimeMillis());
			
			return g1.getTimeInMillis();
		} catch (SQLException e) {
			// TODO Auto-generated catch block
			e.printStackTrace();
		}
		
		return 0;
	}

	public String getName(int iid) {
		String query = "select * from item where iid = "+iid+"";
		System.out.println(query);
		
		try {
			this.db.openConnection();
			Statement stm = this.db.getConnection().createStatement();
			ResultSet rs = stm.executeQuery(query);
			
			rs.next();
			return rs.getString("name");
					
		} catch (SQLException e) {
			// TODO Auto-generated catch block
			e.printStackTrace();
		}
		
		return "";
	}
}
